from tkinter.messagebox import RETRY
import pymssql
from DBUtils.PooledDB import PooledDB
import os
###
# pip install pymysql
# pip install DBUtils==1.3  --- 特别要注意
class TMssqlConnection:
    def __init__(self) -> None:
        self.key = ""

        self.host = "meeting.xmgkfw.com"
        self.port = 42049
        self.userName = "sa"
        self.pwd = "Hhuc1115"
        self.dbName = "modbusTcp"
        self.maxNum = 10
        self.minNum = 1
        self.sqlPath = ""

        self.pool = None

    def setConfig(self, host, port, uid, pwd, db, sqlPath):
        self.host = host
        self.port = port
        self.uid = uid
        self.pwd = pwd
        self.dbName = db
        self.sqlPath = sqlPath

    def getConn(self):
        conn = None
        try:
            if self.pool == None:
                self.pool = PooledDB(
                    creator=pymssql,
                    mincached=1,
                    maxcached=10,
                    host=self.host,
                    port=self.port,
                    database=self.dbName,
                    user=self.userName,
                    password=self.pwd
                )
            conn = self.pool.connection()
            # conn.setAutoCommit(True)
        except Exception as er:
            print(er)
        return conn

    def getSql(self, sqlKey):
        res = ""
        try:
            filePath = os.path.join(self.sqlPath, sqlKey)
            if filePath.find(".txt") < 0:
                filePath = filePath + ".txt"
            if os.path.exists(filePath):
                with open(filePath, "r", encoding='utf-8') as f:
                    res = f.read()
            else:
                print('sqlKey(' + sqlKey + ') is error.')
        except Exception as er:
            print(er)
        return res

    def query(self, sql, params):
        res = {}
        rs = None
        try:
            conn = self.getConn()
            cursor = conn.cursor(as_dict=True)
            if params != None:
                cursor.execute(sql, params)
            else:
                cursor.execute(sql)

            rsList = []

            if cursor.description != None:
                rs = cursor.fetchall()
                rsList.append(list(rs))
                while cursor.nextset():
                    rs = cursor.fetchall()
                    rsList.append(list(rs))
                if len(rsList) == 1:
                    res = rsList[0]
                else:
                    res = rsList
                # res = list(rs)
            else:
                res = {
                    "rowNum": cursor.rownumber,
                    "rowCount": cursor.rowcount,
                    "newId": cursor.lastrowid
                }
            cursor.close()
            cursor = None
            conn.close()
            conn = None
        except Exception as er:
            print(er)
        return res

    def queryBySqlKey(self, sqlKey, params):
        rs = None
        try:
            sql = self.getSql(sqlKey)
            if sql != "":
                rs = self.query(sql, params)
        except Exception as er:
            print(er)
        return rs


class TMssql:
    def __init__(self) -> None:
        self.connections = {}

    def setConnections(self, connectionKey, host, port, uid, pwd, db, sqlPath):
        conn = None
        if connectionKey in self.connections.keys():
            conn = self.connections[connectionKey]
        else:
            conn = TMssqlConnection()
        conn.key = connectionKey
        conn.setConfig(host, port, uid, pwd, db, sqlPath)
        self.connections[connectionKey] = conn

    def routerHandlerQuery(self, result, request, router):
        try:
            ps = router.getRequestParams(request)
            dbKey = ""
            sqlKey = ""
            params = {}
            for p in ps.keys():
                if p == 'dbKey':
                    dbKey = ps["dbKey"]
                elif p == 'sqlKey':
                    sqlKey = ps["sqlKey"]
                else:
                    params[p] = ps[p]

            if dbKey in self.connections.keys():
                client = self.connections[dbKey]
                sql = client.getSql(sqlKey)
                if sql != "":
                    if len(params.keys()) == 0:
                        params = None
                    rs = client.query(sql, params)
                    result["datas"] = rs
                    result["status"] = 1
                else:
                    result["status"] = -2
                    result["error"] = "sqlKey is error"
            else:
                result["status"] = -1
                result["error"] = "dbKey is error"
        except Exception as er:
            print(er)


mssql = TMssql()

mssql.setConnections("modbusTcp",
                     host="meeting.xmgkfw.com",
                     port=42049,
                     uid="sa",
                     pwd="Hhuc1115",
                     db="modbusTcp",
                     sqlPath="D:/project/pysvr/datas/sqls/modbusTcp"
                     )
